/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tesam.gedeon.dao.impl;

import com.tesam.gedeon.dao.ProviderAccountDao;
import com.tesam.gedeon.entity.Provider;
import com.tesam.gedeon.entity.ProviderAccount;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.stereotype.Repository;

/**
 *
 * @author joelpacheco
 */
@Repository
public class ProviderAccountDaoImpl extends SimpleJdbcDaoSupport implements ProviderAccountDao {

    @Autowired
    public ProviderAccountDaoImpl(DataSource dataSource) {
        setDataSource(dataSource);
    }

    @Override
    public int save(ProviderAccount providerAccount) {
        return getJdbcTemplate().update("INSERT INTO provideraccount"
                + "("
                + "createdBy,modifiedby,dateCreated,status,deleted,presence,ownerid,providerid,code,fathercode,name"
                + ")VALUES(?,?,NOW(),?,?,?,?,?,?,?)", providerAccount.getCreatedBy(), 0, 1, 0, 1, 1, providerAccount.getProvider().getId(), providerAccount.getCode().trim(),
                providerAccount.getFathercode(), providerAccount.getName().trim());
    }

    @Override
    public int delete(ProviderAccount providerAccount) {
        return getJdbcTemplate().update("UPDATE provideraccount SET modifiedby = ?, datemodified = NOW(),deleted = 1 WHERE id = ?", providerAccount.getModifiedBy(), providerAccount.getId());
    }

    @Override
    public int update(ProviderAccount providerAccount) {
        return getJdbcTemplate().update("UPDATE provideraccount SET modifiedby = ?,datemodified = NOW(), code = ?,providerid = ?,name = ?,fathercode = ? WHERE id = ?",
                providerAccount.getModifiedBy(), providerAccount.getCode().trim(), providerAccount.getProvider().getId(),
                providerAccount.getName(), providerAccount.getFathercode(), providerAccount.getId());
    }

    @Override
    public ProviderAccount getById(int providerAccountId) {

        RowMapper<ProviderAccount> rowMapper = new RowMapper<ProviderAccount>() {
            @Override
            public ProviderAccount mapRow(ResultSet resultSet, int numRows) throws SQLException {

                ProviderAccount providerAccount = new ProviderAccount();

                providerAccount.setId(resultSet.getInt("id"));
                providerAccount.setCreatedBy(resultSet.getInt("createdby"));
                providerAccount.setDateCreated(resultSet.getTimestamp("datecreated"));
                providerAccount.setDateModified(resultSet.getTimestamp("datemodified"));
                providerAccount.setCode(resultSet.getString("code"));
                providerAccount.setName(resultSet.getString("name"));
                providerAccount.setFathercode(resultSet.getString("fathercode"));

                Provider provider = new Provider(resultSet.getString("providercode"), resultSet.getString("providername"));
                provider.setId(resultSet.getInt("providerid"));

                providerAccount.setProvider(provider);

                return providerAccount;
            }
        };
        return getSimpleJdbcTemplate().queryForObject("SELECT pa.id,pa.createdby,pa.modifiedby, pa.datecreated,pa.datemodified,"
                + " pa.status,pa.code,pa.fathercode,pa.providerid,pa.name,provider.name as providername,provider.code as providercode "
                + " FROM provideraccount pa INNER JOIN provider ON pa.providerid = provider.id AND pa.deleted = 0 "
                + " WHERE pa.id = ?", rowMapper, providerAccountId);
    }

    @Override
    public List<ProviderAccount> getAll() {
        return getSimpleJdbcTemplate().query("SELECT * FROM provideraccount WHERE deleted = 0", new BeanPropertyRowMapper<ProviderAccount>(ProviderAccount.class));
    }

    @Override
    public List<ProviderAccount> getAllByLimit(int startLimit, int endLimit, String dataToSort, String directionOfOrder) {
        String query = null;
        try {

            query = getLimit("SELECT pa.id,pa.createdby,pa.modifiedby, pa.datecreated,pa.datemodified,"
                    + " pa.status,pa.code,pa.providerid,pa.name,provider.name as providername,provider.code as providercode "
                    + " FROM provideraccount pa INNER JOIN provider ON pa.providerid = provider.id AND pa.deleted = 0",
                    startLimit, endLimit, dataToSort, directionOfOrder);
         
            RowMapper<ProviderAccount> rowMapper = new RowMapper<ProviderAccount>() {
                @Override
                public ProviderAccount mapRow(ResultSet resultSet, int rowNum) throws SQLException {
                    ProviderAccount providerAccount = new ProviderAccount();

                    providerAccount.setId(resultSet.getInt("id"));
                    providerAccount.setCreatedBy(resultSet.getInt("createdby"));
                    providerAccount.setDateCreated(resultSet.getTimestamp("datecreated"));
                    providerAccount.setDateModified(resultSet.getTimestamp("datemodified"));
                    providerAccount.setCode(resultSet.getString("code"));
                    providerAccount.setName(resultSet.getString("name"));
                    // System.out.println(resultSet.getInt("providerid"));
                    Provider provider = new Provider(resultSet.getString("providercode"), resultSet.getString("providername"));
                    provider.setId(resultSet.getInt("providerid"));

                    providerAccount.setProvider(provider);

                    return providerAccount;
                }
            };

            return getSimpleJdbcTemplate().query(query, rowMapper);

        } catch (Exception e) {
            System.out.println("getAllByLimit : Exception :" + e.getMessage());
            return null;
        }
    }

    private String getLimit(String query, int startLimit, int endLimit,
            String dataToSort, String directionOfOrder) {

        StringBuilder newQuery = new StringBuilder(query);

        newQuery.append(" ORDER BY ");
        newQuery.append(dataToSort);
        newQuery.append(" ");
        newQuery.append(directionOfOrder);
        newQuery.append(" ");
        newQuery.append("LIMIT ");
        newQuery.append(endLimit);
        newQuery.append(" OFFSET ");
        newQuery.append(startLimit);

        return newQuery.toString();
    }

    @Override
    public List<ProviderAccount> searchByName(ProviderAccount providerAccount, int satartLimit, int endLimit) {
        String query = null;
        try {
            query = getLimit("SELECT pa.id,pa.createdby,pa.modifiedby, pa.datecreated,pa.datemodified,pa.status,pa.code,pa.providerid,pa.name,"
                    + " provider.name as providername,provider.code as providercode FROM provideraccount"
                    + " pa INNER JOIN provider ON pa.providerid = provider.id AND pa.deleted = 0 "
                    + " WHERE upper(pa.name) LIKE ('%'||?||'%')", satartLimit, endLimit, "pa.name", "ASC");

            RowMapper<ProviderAccount> rowMapper = new RowMapper<ProviderAccount>() {
                @Override
                public ProviderAccount mapRow(ResultSet resultSet, int rowNum) throws SQLException {
                    ProviderAccount providerAccount = new ProviderAccount();

                    providerAccount.setId(resultSet.getInt("id"));
                    providerAccount.setCreatedBy(resultSet.getInt("createdby"));
                    providerAccount.setDateCreated(resultSet.getTimestamp("datecreated"));
                    providerAccount.setDateModified(resultSet.getTimestamp("datemodified"));
                    providerAccount.setCode(resultSet.getString("code"));
                    providerAccount.setName(resultSet.getString("name"));
                    // System.out.println(resultSet.getInt("providerid"));
                    Provider provider = new Provider(resultSet.getString("providercode"), resultSet.getString("providername"));
                    provider.setId(resultSet.getInt("providerid"));

                    providerAccount.setProvider(provider);

                    return providerAccount;
                }
            };

            return getSimpleJdbcTemplate().query(query, rowMapper, providerAccount.getName());
        } catch (Exception e) {
            System.out.println("searchByName:Exception:" + e.getMessage());
            return null;
        }
    }

    @Override
    public int getCountSearchByName(ProviderAccount providerAccount) {
        return getSimpleJdbcTemplate().queryForInt("SELECT count(*) FROM provideraccount WHERE deleted = 0 AND upper(name) LIKE ('%' || ? || '%')", providerAccount.getName());
    }

    @Override
    public int getCountTotal() {
        return getSimpleJdbcTemplate().queryForInt("SELECT count(*) FROM provideraccount WHERE deleted = 0");
    }
}
